package fr.umlv.android.caveAvin.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class MaBaseSQLite extends SQLiteOpenHelper {
 
	// table user
	private static final String TABLE_USER= "table_user";
	
	private static final String COL_ID_USER = "Id_user";
	
	private static final String COL_LOGIN = "Login";
	
	private static final String COL_PASS = "PassWord";
	
	private static final String COL_MAIL = "Mail";
	
	// table wine cellar
	private static final String TABLE_WINE_CELAR ="table_wine_celar";
	
	private static final String COL_ID_WINE_CELAR = "Id_wine_celar";
	
	private static final String COL_NAME  ="Name";
	
	private static final String COL_ADDRESS  ="Address";
	
	private static final String COL_POSTALCODE  ="Postal_Code";
	
	private static final String COL_CITY  ="City";
	
	private static final String COL_COUNTRY  ="Country";
	
	private static final String COL_DATECREATION = "Date_Creation";
	
	private static final String COL_CAPACITY  ="Capacity";
	
	private static final String COL_NBBOTTLE  ="NB_Bottle";
	
	//table bottle
	
	private static final String TABLE_BOTTLE= "table_bottle";
	
	private static final String COL_ID_BOTTLE = "Id_bottle";
	
	private static final String COL_DESIGNATION = "designation";
	
	private static final String COL_PRODUCER_NAME = "producer_name";
	
	private static final String COL_DENOMINATION = "denomination";
	
	private static final String COL_VIN_TYPE = "vin_type";
	
	private static final String COL_CONTENT_ALCOHOL = "content_alcohol";
	
	private static final String COL_VIN_ORIGIN= "vin_origin";
	
	private static final String COL_LATITUDE = "latitude";
	
	private static final String COL_LONGITUDE = "longitude";
	
	private static final String COL_COUNTRY_ORIGIN = "country_origin";
	
	private static final String COL_CAPACITYBOTTLE = "capacity";
	
	private static final String COL_COLOR = "color";
	
	private static final String COL_VIN_SPECIFICATION = "vin_specification";
	
	private static final String COL_GRAPE_HARVEST_YEARS = "grape_harvest_years";
	
	private static final String COL_MATURATION_PERIOD = "maturation_period";
	
	// jointure table bottle & wine cellar.
	private static final String TABLE_BOTTLE_WINE_CELAR = "table_bottle_wine_celar";
	
	private static final String COL_ID_WINE_CELAR_FK = "Id_wine_celar_fk";
	
	private static final String COL_ID_BOTTLE_FK = "Id_bottle_fk";
	
	private static final String COL_DATE_ADD = "date_add";
	
	
 
	private static final String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_USER + " ("
						+ COL_ID_USER + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
						+ COL_LOGIN + " TEXT NOT NULL, "
						+ COL_MAIL + " , "
						+ COL_PASS + " TEXT NOT NULL);";
	
	private static final String CREATE_TABLE_WINE_CELAR = "CREATE TABLE " + TABLE_WINE_CELAR + " ("
						+ COL_ID_WINE_CELAR + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
						+ COL_NAME + " TEXT NOT NULL, "
						+ COL_ADDRESS + " , "
						+ COL_POSTALCODE + " , "
						+ COL_CITY + " , "
						+ COL_COUNTRY + " , "
						+ COL_DATECREATION + " , "
						+ COL_CAPACITY + " , "
						+ COL_NBBOTTLE + " , "
						+ COL_ID_USER + ");";

	private static final String CREATE_TABLE_BOTTLE = "CREATE TABLE " + TABLE_BOTTLE + " ("
	+ COL_ID_BOTTLE + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
	+ COL_DESIGNATION + " TEXT NOT NULL, "
	+ COL_PRODUCER_NAME + " , "
	+ COL_DENOMINATION + " , "
	+ COL_VIN_TYPE + " , "
	+ COL_CONTENT_ALCOHOL + " , "
	+ COL_VIN_ORIGIN + " , "
	+ COL_LATITUDE + " , "
	+ COL_LONGITUDE + " , "
	+ COL_COUNTRY_ORIGIN + " , "
	+ COL_CAPACITYBOTTLE + " , "
	+ COL_COLOR + " , "
	+ COL_VIN_SPECIFICATION + " , "
	+ COL_GRAPE_HARVEST_YEARS + " , "
	+ COL_MATURATION_PERIOD + ");";
	
	private static final String CREATE_TABLE_BOTTLE_WINE_CELAR = "CREATE TABLE " + TABLE_BOTTLE_WINE_CELAR + " ("
	+ COL_ID_WINE_CELAR_FK + " NOT NULL, " 
	+ COL_ID_BOTTLE_FK + " NOT NULL, "
	+ COL_DATE_ADD + " TEXT NOT NULL);";
	
 
	public MaBaseSQLite(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {
		//on créé la table à partir de la requête écrite dans la variable CREATE_BDD
		db.execSQL(CREATE_TABLE_USER);
		System.out.println("La table "+TABLE_USER+" est cree ..................OK.");
		db.execSQL(CREATE_TABLE_WINE_CELAR);
		System.out.println("La table "+TABLE_WINE_CELAR+" est cree ..................OK.");
		db.execSQL(CREATE_TABLE_BOTTLE);
		System.out.println("La table "+TABLE_BOTTLE+" est cree ..................OK.");
		db.execSQL(CREATE_TABLE_BOTTLE_WINE_CELAR);
		System.out.println("La table "+TABLE_BOTTLE_WINE_CELAR+" est cree ..................OK.");
	}
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//On peut fait ce qu'on veut ici moi j'ai décidé de supprimer la table et de la recréer
		//comme ça lorsque je change la version les id repartent de 0
		// je supprime aussi parce qu'on peut pas recreer une table qui existe.
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER + ";");
		System.out.println("Suppression de la table "+TABLE_USER+" .................OK.");
		
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_WINE_CELAR + ";");
		System.out.println("Suppression de la table "+TABLE_WINE_CELAR+" .............. OK.");
		
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOTTLE + ";");
		System.out.println("Suppression de la table "+TABLE_BOTTLE+".................... OK.");
		
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOTTLE_WINE_CELAR + ";");
		System.out.println("Suppression de la table "+TABLE_BOTTLE_WINE_CELAR+".................... OK.");
		
		onCreate(db);
	}


}

